#商品管理系统
#1、商品添加   商品已存在，提示不能添加
#    名字：苹果  价格：5   库存：200
#2、查看商品   商品不存在？  按名字查看
#3、删除商品   按名字删除
#4、修改商品   按名字修改， 价格和库存
#5、退出
#可以不限次数输入

import json
import pymysql
# 创建数据库连接
db = pymysql.connect(
    host = "192.168.138.131",
    user = "yang",
    passwd = "123456",
    port = 3306,
    db = "test2")
# grant all on *.* to 'yang'@'%' identified by '123456'

#创建游标 --- 真正的执行sql语句
cur = db.cursor()

#统一执行sql语句函数
def execute_sql(sql,params=None):
    if params:
        cur.execute(sql,params)
    else:
        cur.execute(sql)
    db.commit()

#加载json文件
def load_product():
    with open("homework.json", "r+", encoding="utf-8") as f:
        json_str = f.read()
        if json_str:
            return json.loads(json_str)
        else:
            return {}

#保存数据到json文件
def save_product(product):
    with open("homework.json","w",encoding="utf-8") as fp:
        fp.write(json.dumps(product,ensure_ascii=False))


# 获取用户输入，确保输入有效
def get_user_input(prompt, valid_fn=None):
    while True:
        user_input = input(prompt)
        if valid_fn and not valid_fn(user_input):
            print("输入无效，请重新输入！")
        else:
            return user_input



#增加商品
def add_product(product):
    add_name = get_user_input("请输入想要添加的商品名称：")
    if add_name in product:
        print("商品已存在，不能添加")
        return
    else:
        pro_inf = {}
        add_price = get_user_input("请输入商品价格：", valid_fn=lambda x: x.isdigit())
        add_stock = get_user_input("请输入商品数量：", valid_fn=lambda x: x.isdigit())
        pro_inf["price"] = add_price
        pro_inf["stock"] = add_stock
        product[add_name] = pro_inf
        print(f"已成功添加{add_name}商品，信息为：{product}")
       ######
        sql = f"INSERT INTO product(name, price, stock) VALUES('{add_name}', '{add_price}', '{add_stock}')"
        execute_sql(sql)
        return

#查看商品
def view_product(product):
    check_pro = get_user_input("请输入要查看的商品：")
    if check_pro in product:
        print(f"此商品的信息为：{product[check_pro]}")
        return
    else:
        print("此商品不存在")
        return

#删除商品
def delete_product(product):
    delete_pro = get_user_input("请输入想删除的商品：")
    if delete_pro in product:
        ret = product.pop(delete_pro)
        print(f"已删除商品{delete_pro},它的信息：{ret}")
        sql = "DELETE FROM product WHERE name = %s"
        execute_sql(sql, (delete_pro,))
        return
    else:
        print("此商品不在库存")
        return


#修改商品信息
def update_product(product):
    change_pro = get_user_input("请输入要修改的商品名：")
    if change_pro not in product:
        print("输入有误")
        return
    else:
        change_price = get_user_input(f"请输入{change_pro}的新价格：", valid_fn=lambda x: x.isdigit())
        change_stock = get_user_input(f"请输入{change_pro}的新数量：", valid_fn=lambda x: x.isdigit())
        product[change_pro]["price"] = change_price
        product[change_pro]["stock"] = change_stock
        print(product)
        sql = "UPDATE product SET price = %s , stock = %s WHERE name = %s"
        execute_sql(sql,(change_price, change_stock, change_pro))
        return

#主函数
def main():
    product = load_product()
    while True:
        choose = get_user_input("请输入操作序号（1-5）：",valid_fn=lambda x: x.isdigit())
        if choose == "5":
            print("已退出系统")
            break
        elif choose == "1":
            add_product(product)
        elif choose == "2":
            view_product(product)
        elif choose == "3":
            delete_product(product)
        elif choose == "4":
            update_product(product)
        else:
            print("请输入有效数字(1-5)")
    save_product(product)
    cur.close()
    db.close()

if __name__ == "__main__":
    main()











# with open("homework.json","r+",encoding="utf-8") as f:
#     json_str = f.read()
#     if json_str:
#         product = json.loads(json_str)
#     else:
#         product = {}
#     while True:
#         choose = input("请输入操作序号（1-5）：")
#         if choose == "5":
#             print("已退出系统")
#             break
#         elif choose == "1":
#             add_name = input("请输入想要添加的商品名称：")
#             if add_name in product:
#                 print("商品已存在，不能添加")
#             else:
#                 pro_inf = {}
#                 add_price = input("请输入商品价格：")
#                 add_stock = input("请输入商品数量：")
#                 pro_inf["price"] = add_price
#                 pro_inf["stock"] = add_stock
#                 product[add_name] = pro_inf
#                 print(f"已成功添加{add_name}商品，信息为：{product}")
#
#                 sql = f"INSERT INTO product(name, price, stock) VALUES('{add_name}', '{add_price}', '{add_stock}')"
#                 cur.execute(sql)
#                 db.commit()
#         elif choose == "2":
#             check_pro = input("请输入要查看的商品：")
#             if check_pro in product:
#                 print(f"此商品的信息为：{product[check_pro]}")
#             else:
#                 print("此商品不存在")
#         elif choose == "3":
#             delete_pro = input("请输入想删除的商品：")
#             ret = product.pop(delete_pro, None)
#             if ret is None:
#                 print("此商品不在库存")
#             else:
#                 print(f"已删除商品{delete_pro},它的信息：{ret}")
#                 sql = "DELETE FROM product WHERE name = %s"
#                 cur.execute(sql,(delete_pro,))
#                 db.commit()
#         elif choose == "4":
#             change_pro = input("请输入要修改的商品名：")
#             if change_pro not in product:
#                 print("输入有误")
#             else:
#
#                 change_price = input(f"请输入{change_pro}的新价格：")
#                 change_stock = input(f"请输入{change_pro}的新数量：")
#                 product[change_pro]["price"] = change_price
#                 product[change_pro]["stock"] = change_stock
#                 print(product)
#                 sql = "UPDATE product SET price = %s , stock = %s WHERE name = %s"
#                 cur.execute(sql,(change_price,change_stock,change_pro))
#                 db.commit()
#         else:
#             print("请输入数字1-5")
#     cur.close()
#     db.close()
#     with open("homework.json","w",encoding="utf-8") as fp:
#         fp.write(json.dumps(product,ensure_ascii=False))